<meta charset="utf-8">
(#) Using STRING instead of TEXT

!!! WARNING: Using STRING instead of TEXT
   This is a warning.

Id
:   `SQLiteString`
Summary
:   Using STRING instead of TEXT
Severity
:   Warning
Category
:   Correctness
Platform
:   Any
Vendor
:   Android Open Source Project
Feedback
:   https://issuetracker.google.com/issues/new?component=192708
Since
:   1.3.0 (July 2015)
Affects
:   Kotlin and Java files
Editing
:   This check runs on the fly in the IDE editor
See
:   https://www.sqlite.org/datatype3.html
Implementation
:   [Source Code](https://cs.android.com/android-studio/platform/tools/base/+/mirror-goog-studio-main:lint/libs/lint-checks/src/main/java/com/android/tools/lint/checks/SQLiteDetector.java)
Tests
:   [Source Code](https://cs.android.com/android-studio/platform/tools/base/+/mirror-goog-studio-main:lint/libs/lint-tests/src/test/java/com/android/tools/lint/checks/SQLiteDetectorTest.java)

In SQLite, any column can store any data type; the declared type for a
column is more of a hint as to what the data should be cast to when
stored.

There are many ways to store a string. `TEXT`, `VARCHAR`, `CHARACTER`
and `CLOB` are string types, **but `STRING` is not**. Columns defined as
STRING are actually numeric.

If you try to store a value in a numeric column, SQLite will try to cast
it to a float or an integer before storing. If it can't, it will just
store it as a string.

This can lead to some subtle bugs. For example, when SQLite encounters a
string like `1234567e1234`, it will parse it as a float, but the result
will be out of range for floating point numbers, so `Inf` will be
stored! Similarly, strings that look like integers will lose leading
zeroes.

To fix this, you can change your schema to use a `TEXT` type instead.

(##) Example

Here is an example of lint warnings produced by this check:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~text
src/test/pkg/SQLiteTest.java:25:Warning: Using column type STRING; did
you mean to use TEXT? (STRING is a numeric type and its value can be
adjusted; for example, strings that look like integers can drop leading
zeroes. See issue explanation for details.) [SQLiteString]
    db.execSQL("CREATE TABLE " + name + "(" + Tables.AppKeys.SCHEMA + ");"); // ERROR
    -----------------------------------------------------------------------
src/test/pkg/SQLiteTest.java:30:Warning: Using column type STRING; did
you mean to use TEXT? (STRING is a numeric type and its value can be
adjusted; for example, strings that look like integers can drop leading
zeroes. See issue explanation for details.) [SQLiteString]
    db.execSQL(TracksColumns.CREATE_TABLE); // ERROR
    --------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here are the relevant source files:

`src/test/pkg/TracksColumns.java`:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~java linenumbers
package test.pkg;

import android.provider.BaseColumns;

public interface TracksColumns extends BaseColumns {

    String TABLE_NAME = "tracks";

    String NAME = "name";
    String CATEGORY = "category";
    String STARTTIME = "starttime";
    String MAXGRADE = "maxgrade";
    String MAPID = "mapid";
    String TABLEID = "tableid";
    String ICON = "icon";

    String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " ("
            + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + NAME + " STRING, "
            + CATEGORY + " STRING, "
            + STARTTIME + " INTEGER, "
            + MAXGRADE + " FLOAT, "
            + MAPID + " STRING, "
            + TABLEID + " STRING, "
            + ICON + " STRING"
            + ");";
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

`src/test/pkg/SQLiteTest.java`:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~java linenumbers
package test.pkg;

import android.database.sqlite.SQLiteDatabase;

@SuppressWarnings({"unused", "SpellCheckingInspection"})
public class SQLiteTest {
    public interface Tables {
        interface AppKeys {
            String NAME = "appkeys";

            interface Columns {
                String _ID = "_id";
                String PKG_NAME = "packageName";
                String PKG_SIG = "signatureDigest";
            }

            String SCHEMA =
                    Columns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                            Columns.PKG_NAME + " STRING NOT NULL," +
                            Columns.PKG_SIG + " STRING NOT NULL";
        }
    }

    public void test(SQLiteDatabase db, String name) {
        db.execSQL("CREATE TABLE " + name + "(" + Tables.AppKeys.SCHEMA + ");"); // ERROR

    }

    public void onCreate(SQLiteDatabase db) {
        db.execSQL(TracksColumns.CREATE_TABLE); // ERROR
    }

    private void doCreate(SQLiteDatabase db) {
        // Not yet handled; we need to flow string concatenation across procedure calls
        createTable(db, Tables.AppKeys.NAME, Tables.AppKeys.SCHEMA); // ERROR
    }

    private void createTable(SQLiteDatabase db, String tableName, String schema) {
        db.execSQL("CREATE TABLE " + tableName + "(" + schema + ");");
    }
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You can also visit the
[source code](https://cs.android.com/android-studio/platform/tools/base/+/mirror-goog-studio-main:lint/libs/lint-tests/src/test/java/com/android/tools/lint/checks/SQLiteDetectorTest.java)
for the unit tests for this check to see additional scenarios.

The above example was automatically extracted from the first unit test
found for this lint check, `SQLiteDetector.test`.
To report a problem with this extracted sample, visit
https://issuetracker.google.com/issues/new?component=192708.

(##) Suppressing

You can suppress false positives using one of the following mechanisms:

* Using a suppression annotation like this on the enclosing
  element:

  ```kt
  // Kotlin
  @Suppress("SQLiteString")
  fun method() {
     execSQL(...)
  }
  ```

  or

  ```java
  // Java
  @SuppressWarnings("SQLiteString")
  void method() {
     execSQL(...);
  }
  ```

* Using a suppression comment like this on the line above:

  ```kt
  //noinspection SQLiteString
  problematicStatement()
  ```

* Using a special `lint.xml` file in the source tree which turns off
  the check in that folder and any sub folder. A simple file might look
  like this:
  ```xml
  &lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;lint&gt;
      &lt;issue id="SQLiteString" severity="ignore" /&gt;
  &lt;/lint&gt;
  ```
  Instead of `ignore` you can also change the severity here, for
  example from `error` to `warning`. You can find additional
  documentation on how to filter issues by path, regular expression and
  so on
  [here](https://googlesamples.github.io/android-custom-lint-rules/usage/lintxml.md.html).

* In Gradle projects, using the DSL syntax to configure lint. For
  example, you can use something like
  ```gradle
  lintOptions {
      disable 'SQLiteString'
  }
  ```
  In Android projects this should be nested inside an `android { }`
  block.

* For manual invocations of `lint`, using the `--ignore` flag:
  ```
  $ lint --ignore SQLiteString ...`
  ```

* Last, but not least, using baselines, as discussed
  [here](https://googlesamples.github.io/android-custom-lint-rules/usage/baselines.md.html).

<!-- Markdeep: --><style class="fallback">body{visibility:hidden;white-space:pre;font-family:monospace}</style><script src="markdeep.min.js" charset="utf-8"></script><script src="https://morgan3d.github.io/markdeep/latest/markdeep.min.js" charset="utf-8"></script><script>window.alreadyProcessedMarkdeep||(document.body.style.visibility="visible")</script>